package co.th.bjc.md.helper.entries;

import java.sql.Connection;
import java.util.Date;

import co.th.bjc.md.dao.CreateUpdate;
import co.th.bjc.md.helper.db.ConnectDB;

public class HelperDatabse extends ConnectDB{
	
	public Connection getConnection() throws Exception{
		return new ConnectDB().getConnectionDB();
	}
	
	public CreateUpdate getCreateUpdate(){
		CreateUpdate object = new CreateUpdate();
		Date nowDate = new Date();
		object.setCreateBy("SYSTEM");
		object.setCreateDate(nowDate);
		object.setUpdateBy("SYSTEM");
		object.setUpdateDate(nowDate);
		return object;
	}
	
	
	
	protected static final String FindProductImageByProdImgId = "SELECT * FROM images WHERE image_id = ?"; 

	public interface IMAGES{
		static final String FIND_BY_IMAGE_ID = "SELECT * FROM images WHERE image_id = ?";
		static final String FIND_BY_PRODUCT_CODE = "SELECT * FROM images WHERE product_code = ?";
		static final String SEQUENCE = "SELECT MAX(IMAGE_ID) FROM IMAGES";
		static final String INSERT = "INSERT INTO IMAGES (IMAGE_NAME,IMAGE_DATA,PRODUCT_CODE, create_by, create_date, update_by, update_date) VALUES(?,?,?,?,?,?,?)";
		static final String DELETE = "DELETE FROM images WHERE image_id = ?";
		static final String UPDATE = "UPDATE images SET image_name = ?,image_data = ?,create_by = ?, create_date = ?,update_by = ?,update_date = ? WHERE image_id = ?";
	}
	
	public interface PRODUCT{
		static final String SEQUENCE = "SELECT MAX(product_code) FROM PRODUCT";
		static final String FIND_ALL = "SELECT * FROM PRODUCT";
		static final String FIND_BY_PRODUCT_NAME_AND_SUPPLIER_CODE = "SELECT * FROM PRODUCT aTable INNER JOIN SUPPLIER bTable " +
				"ON aTable.supplier_code = bTable.supplier_code ";
		static final String INSERT = "INSERT INTO product (product_code,product_name,product_detail,supplier_code,active,create_by,create_date,update_by,update_date) VALUES (?,?,?,?,?,?,?,?,?)";
		static final String DELETE = "DELETE FROM PRODUCT WHERE PRODUCT_CODE = ?";
		static final String UPDATE = "UPDATE product SET product_name = ?,product_detail = ?,supplier_code = ?,active = ?, update_by = ?,update_date = ? WHERE product_code = ?";
	}
	
	public interface SUPPLIER{
		static final String FIND_ALL = "SELECT * FROM SUPPLIER ";
		static final String FIND_BY_SUPPLIER_CODE = "SELECT * FROM SUPPLIER WHERE SUPPLIER_CODE = ?";
	}
	
	public interface DEPARTMENT{
		static final String SEQUENCE = "SELECT MAX(department_code) FROM DEPARTMENT";
		static final String FIND_ALL = "SELECT * FROM DEPARTMENT ";
		static final String FIND_BY_DEPARTMENT_CODE = "SELECT * FROM DEPARTMENT WHERE DEPARTMENT_CODE = ?";
		static final String INSERT = "INSERT INTO DEPARTMENT(department_code, department_name, department_detail, create_by, create_date, update_by, update_date)VALUES (?,?,?,?,?,?,?)";
		static final String UPDATE = "UPDATE department SET department_name = ?, department_detail = ?, update_by = ?, update_date = ? WHERE department_code = ?;";
		static final String DELETE = "DELETE FROM DEPARTMENT WHERE department_code = ?";
	}
	
	public interface IMP{
		static final String FIND_ALL = "SELECT * FROM rating_imp ";
		static final String FIND_BY_IMP_CODE = "SELECT * FROM rating_imp WHERE imp_code = ?";
	}
	
	public interface RP{
		static final String FIND_ALL = "SELECT * FROM rating_rp ";
		static final String FIND_BY_RP_CODE = "SELECT * FROM rating_rp WHERE rp_code = ?";
	}
	
	public interface CUSTOMER_DATA{
		static final String FIND_ALL = "SELECT * FROM customer_data";
		static final String FIND_BY_ID = "SELECT * FROM customer_data WHERE customer_data_id = ? ";
		static final String FIND_BY_CRITERIA = "SELECT * FROM customer_data WHERE type = ? OR hospital_department  ? OR (bed_capacity_min <= ? AND bed_capacity_max = ?) OR president_imp_rate = ? OR director_imp_rate = ? OR directorRpRate = ? LIMIT  ? , ?";
		static final String INSERT = "INSERT INTO customer_data (" +
				"customer_data_id, customer_data_code, bed_capacity_max, bed_capacity_min, hospital_address, " +
				"hospital_email, hospital_telephone, hospital_department, director_imp_rate, director_name, " +
				"director_email, director_telephone, director_rp_rate, hospital_name_th, hospital_name_en, " +
				"president_imp_rate, president_name, president_email, president_telephone, president_rp_rate, " +
				"sale_person, type, bjc_brand, bjc_category, bjc_department, bjc_model, region, st_competitor, " +
				"nd_competitor, rd_competitor, remark1, remark2,img) " +
				"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		static final String DELETE = "DELETE FROM customer_data WHERE customer_data_id = ?";
	}
}
